package Dao.Impl;

import DBUtil.DBUtil;
import Dao.staffsDao;
import entity.staffs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 20x
 * @date 2025 - 02 -24
 **/
public class staffsDaoImpl implements staffsDao {

    @Override
    public int addStaff(staffs staffs) {
       String sql = "insert into staffs values(?,?,?,?,?,?,?)";
       PreparedStatement pst = null;
       Connection conn = DBUtil.getConnection();
       try{
           pst = conn.prepareStatement(sql);
           pst.setString(1, staffs.getId());
           pst.setString(2, staffs.getName());
           pst.setString(3, staffs.getAge());
           pst.setString(4, staffs.getPhone());
           pst.setString(5, staffs.getSta_pos());
           pst.setString(6, staffs.getAdd_time());
           pst.setString(7, staffs.getUpdate_time());
          return pst.executeUpdate();
       }
       catch (Exception e){
           e.printStackTrace();
       }
       return 0;
    }

    @Override
    public int deleteStaff(String id) {
        String sql = "delete from staffs where id = ?";
        PreparedStatement pst = null;
        Connection conn = DBUtil.getConnection();
        try{
            pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            return pst.executeUpdate();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateStaff(staffs staffs) {
        String sql = "update staffs set name = ?,age = ?,phone = ?,sta_pos = ?,update_time = ? where id = ?";
        PreparedStatement pst = null;
        Connection conn = DBUtil.getConnection();
        try{
            pst = conn.prepareStatement(sql);
            pst.setString(1, staffs.getName());
            pst.setString(2, staffs.getAge());
            pst.setString(3, staffs.getPhone());
            pst.setString(4, staffs.getSta_pos());
            pst.setString(5, staffs.getUpdate_time());
            pst.setString(6, staffs.getId());
            return pst.executeUpdate();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public staffs queryStaffById(String id) {
        staffs staff = null;
        String sql = "select * from staffs where id = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DBUtil.getConnection();
        try{
            pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            rs = pst.executeQuery();
            while (rs.next()){
                staff = new staffs(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }

        return staff;
    }

    @Override
    public List<staffs> queryAllStaff() {
        List<staffs> staffs = new ArrayList<>();
        String sql = "select * from staffs";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DBUtil.getConnection();
        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()){
                staffs staff = new staffs(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
                staffs.add(staff);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }

        return staffs;
    }

    @Override
    public List<staffs> queryStaffsLimit(int page, int limit) {
        List<staffs> staffs = new ArrayList<>();
        String sql = "select * from staffs limit ?,?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DBUtil.getConnection();
        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, (page-1)*limit);
            pst.setInt(2, limit);
            rs = pst.executeQuery();
            while (rs.next()){
                staffs staff = new staffs(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
                staffs.add(staff);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return staffs;
    }

    public static void main(String[] args) {
//        查所有
//        System.out.println(new staffsDaoImpl().queryAllStaff());

//        查单个
//        System.out.println(new staffsDaoImpl().queryStaffById("1"));

//        删除
//        System.out.println(new staffsDaoImpl().deleteStaff("1"));

//        添加
//        staffs staff = new staffs("7","张7","20","123456789","经理","2025-02-24","2025-02-24");
//        System.out.println(new staffsDaoImpl().addStaff(staff));

//        修改
//        staffs staff = new staffs("1","张三3","20","123456789","经理","2025-02-24","2025-02-24");
//        System.out.println(new staffsDaoImpl().updateStaff(staff));

//        分页查询
        System.out.println(new staffsDaoImpl().queryStaffsLimit(2, 5));

    }
}
